package com.guet.niit.Admin.dao;

import com.guet.niit.Admin.pojo.ChargingPile;
import com.guet.niit.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChargingPileDaolmpl {

    /**
     * 充电桩添加
     * 1.SQL: "INSERT INTO charging_pile (station_id,id,service_charge,type,price,status) "+
     *                     "VALUES("
     *                     + chargingPile.getId()
     *                     + "','"
     *                     + chargingPile.getStation_id()
     *                     + "','"
     *                     + chargingPile.getService_charge()
     *                     + "','"
     *                     + chargingPile.getType()
     *                     + "','"
     *                     + chargingPile.getStatus()
     *                     + "')";
     *
     * @return
     */
    public boolean chargingPileAdd(ChargingPile chargingPile){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            //ChargingPile chargingPile = new ChargingPile(station_id,id,service_charge,type,price,status);
//            String sql = "INSERT INTO charging_pile (station_id,id,service_charge,type,price,status) "+
//                    "VALUES("
//                    + chargingPile.getId()
//                    + "','"
//                    + chargingPile.getStation_id()
//                    + "','"
//                    + chargingPile.getService_charge()
//                    + "','"
//                    + chargingPile.getType()
//                    + "','"
//                    + chargingPile.getStatus()
//                    + "')";
            String sql = "insert into charging_pile(station_id,id,service_charge,type,price,status) values (?,?,?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,chargingPile.getStation_id());
            pstmt.setInt(2,chargingPile.getId());
            pstmt.setDouble(3,chargingPile.getService_charge());
            pstmt.setInt(4,chargingPile.getType());
            pstmt.setDouble(5,chargingPile.getPrice());
            pstmt.setInt(6,chargingPile.getStatus());

            int num = pstmt.executeUpdate();
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn,stmt,rs);
        }
        return false;
    }


    /**
     * 充电桩删除
     * 1.SQL:"DELETE FROM charging_pile WHERE id=" + id;
     *
     * @return
     */
    public boolean chargingPileDelete(int id){

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            connection = JDBCUtil.createConnection();
            // 获得Statement对象
            statement = connection.createStatement();
            // 发送SQL语句
            String sql = "DELETE FROM charging_pile WHERE id=" + id;
            int num = statement.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }
        return false;

    }


//    /**
//     * 充电桩修改
//     * 1.SQL:"UPDATE charging set station_id='" + chargingPile.getStation_id()+ "',service_charge='" +chargingPile.getService_charge()
//     *                     + "',type='" +chargingPile.getType()+ "',status='" +chargingPile.getStatus()
//     *                     + "' WHERE id=" + chargingPile.getId();
//     *
//     * @return
//     */
//    public boolean chargingPileModify(Long station_id, int id, Double service_charge, int type, Double price, int status){
//        Connection conn = null;
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            // 获得数据的连接
//            conn = JDBCUtil.createConnection();
//            // 获得Statement对象
//            stmt = conn.createStatement();
//            // 发送SQL语句
//            ChargingPile chargingPile = new ChargingPile(station_id,id,service_charge,type,price,status);
//            String sql =  "UPDATE charging_pile set station_id='" + chargingPile.getStation_id()+ "',service_charge='" +chargingPile.getService_charge()
//                    + "',type='" +chargingPile.getType()+ "',status='" +chargingPile.getStatus()
//                    + "' WHERE id=" + chargingPile.getId();
//
//            int num = stmt.executeUpdate(sql);
//            if (num > 0) {
//                return true;
//            }
//            return false;
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            JDBCUtil.closeConnection(conn, stmt,rs );
//        }
//        return false;
//    }

    /**
     * 充电桩修改
     * 1.SQL:"UPDATE charging set station_id='" + chargingPile.getStation_id()+ "',service_charge='" +chargingPile.getService_charge()
     *                     + "',type='" +chargingPile.getType()+ "',status='" +chargingPile.getStatus()
     *                     + "' WHERE id=" + chargingPile.getId();
     *
     * @return
     */
    public boolean chargingPileModify(Long station_id, int id, Double service_charge, int type, Double price, int status){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = JDBCUtil.createConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            ChargingPile chargingPile = new ChargingPile(station_id,id,service_charge,type,price,status);
            String sql =  "UPDATE charging_pile set station_id='" + chargingPile.getStation_id()+ "',service_charge='" +chargingPile.getService_charge()
                    + "',type='" +chargingPile.getType()+ "',price='"+chargingPile.getPrice()+ "',status='" +chargingPile.getStatus()
                    + "' WHERE id=" + chargingPile.getId();

            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, stmt,rs );
        }
        return false;
    }



    /**
     * 充电桩查询
     * 1.SQl:select * from charging_pile;
     * 2.结果：List<Brand>
     */
    public List<ChargingPile> chargingPileInquire(){
        Connection connection = null;
        try {
            connection = JDBCUtil.createConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        Statement statement = null;
        ResultSet rs = null;
        ChargingPile chargingPile = null;
        ArrayList<ChargingPile> arrayList;
        try {

            PreparedStatement preparedStatement = null;
            String sql = "select * from charging_pile";
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            arrayList = new ArrayList<>();
            while (rs.next()) {
                Long station_id = rs.getLong("station_id");
                int id = rs.getInt("id");
                Double service_charge = rs.getDouble("service_charge");
                int type = rs.getInt("type");
                Double price = rs.getDouble("price");
                int status = rs.getInt("status");
                chargingPile = new ChargingPile(station_id,id,service_charge,type,price,status);
                arrayList.add(chargingPile);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            JDBCUtil.closeConnection(connection, statement, rs);
        }

        return arrayList;


    }



}

